package kernel_db.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import common.entities.enums.SEX;
import common.entities.persistent.Actor;
import common.entities.persistent.Country;
import common.entities.persistent.Director;
import common.entities.persistent.Genre;
import common.entities.persistent.Movie;
import common.entities.persistent.User;

public class Selector {
	public static synchronized List<User> getUsers(String userNameSubStr, int from, int to, DB db) throws DB_Error {
		int startResults = from ;
		int lengthOfResults = to - from ;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<User> users = new ArrayList<User>();
		
		try {
			stmt = conn.createStatement();
			if (userNameSubStr == null || userNameSubStr.length() == 0)
				rs = stmt.executeQuery("SELECT * FROM user ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			else
				rs = stmt.executeQuery("SELECT * FROM user WHERE lower(name) LIKE '%"+userNameSubStr.toLowerCase()+"%' ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				User user = new User(rs.getInt("id"),rs.getString("name"),rs.getString("password"),rs.getInt("games_played"),rs.getLong("money_won"),rs.getInt("highest_win"));
				users.add(user);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get users - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return users;
	}
	
	public static synchronized List<Genre> getGenres(String GenreSubStr, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Genre> genres = new ArrayList<Genre>();
		
		try {
			stmt = conn.createStatement();
			if (GenreSubStr == null ||GenreSubStr.length() == 0)
				rs = stmt.executeQuery("SELECT * FROM genre ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			else
				rs = stmt.executeQuery("SELECT * FROM genre WHERE lower(name) LIKE '%"+GenreSubStr.toLowerCase()+"%' ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Genre genre = new Genre(rs.getInt("id"),rs.getString("name"));
				genres.add(genre);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get genres - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return genres;
	}

	public static synchronized List<Country> getCountries(String countrySubStr, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Country> countries = new ArrayList<Country>();
		
		try {
			stmt = conn.createStatement();
			if (countrySubStr == null || countrySubStr.length() == 0)
				rs = stmt.executeQuery("SELECT * FROM country ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			else
				rs = stmt.executeQuery("SELECT * FROM country WHERE lower(name) LIKE '%"+countrySubStr.toLowerCase()+"%' ORDER BY name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Country country = new Country(rs.getInt("id"),rs.getString("name"));
				countries.add(country);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get countries - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return countries;
	}

	public static synchronized List<Actor> getActors(String firstNameSubStr, String lastNameSubStr, SEX sex, Movie movie, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Actor> actors = new ArrayList<Actor>();
		
		String table = "actor";
		String where = "";
		if (firstNameSubStr!=null && firstNameSubStr.length()>0)
		{
			where += "lower(first_name) LIKE '%" + firstNameSubStr.toLowerCase() + "%' AND ";
		}
		if (lastNameSubStr!=null && lastNameSubStr.length()>0)
		{
			where += "lower(last_name) LIKE '%" + lastNameSubStr.toLowerCase() + "%' AND ";
		}
		if (sex!=null)
		{
			where += "lower(Sex)=" + sex.getValue() + " AND ";
		}
		if (movie!=null)
		{
			table += ",actor_movie";
			where += "movie_id=" + movie.getId() + " AND  actor_id=actor.id AND ";
		}
		
		if (where!="")
		{
			where = where.substring(0,where.length()-5);//deleting the last 5 characters that are always " AND "
			where = " WHERE " + where;
		}
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM "+ table + where +" ORDER BY actor.last_name, actor.first_name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Actor actor = new Actor(rs.getInt("id"),rs.getString("first_name"),rs.getString("last_name"),rs.getInt("Sex")==1?SEX.MALE:SEX.FEMALE);
				actors.add(actor);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get actors - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return actors;
	}
	
	public static synchronized List<Director> getDirectors(String firstNameSubStr, String lastNameSubStr, Movie movie, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Director> directors = new ArrayList<Director>();
		
		String table = "director";
		String where = "";
		if (firstNameSubStr!=null && firstNameSubStr.length()>0)
		{
			where += "lower(first_name) LIKE '%" + firstNameSubStr.toLowerCase() + "%' AND ";
		}
		if (lastNameSubStr!=null && lastNameSubStr.length()>0)
		{
			where += "lower(last_name) LIKE '%" + lastNameSubStr.toLowerCase() + "%' AND ";
		}
		if (movie!=null)
		{
			table += ",director_movie";
			where += "movie_id=" + movie.getId() + " AND  director_id=director.id AND ";
		}
		
		if (where!="")
		{
			where = where.substring(0,where.length()-5);//deleting the last 5 characters that are always " AND "
			where = " WHERE " + where;
		}
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM "+ table + where +" ORDER BY director.last_name, director.first_name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Director director = new Director(rs.getInt("id"),rs.getString("first_name"),rs.getString("last_name"));
				directors.add(director);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get directors - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return directors;
	}

	public static synchronized List<Movie> getMovies(String titleSubStr, int releaseYearMin,
			int releaseYearMax, float rankMin, float rankMax, Actor actor,
			Director director, List<Genre> geners, Country country, int from,
			int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Movie> movies = new ArrayList<Movie>();
		
		String table = "movie";
		String where = "";
		if (titleSubStr!=null && titleSubStr.length()>0)
		{
			where += "lower(name) LIKE '%" + titleSubStr + "%' AND ";
		}
		if (releaseYearMin!=-1)
		{
			where += "release_year>="+releaseYearMin+" AND ";
		}
		if (releaseYearMax!=-1)
		{
			where += "release_year<="+releaseYearMax+" AND ";
		}
		if (rankMin!=-1)
		{
			where += "rank>="+rankMin+" AND ";
		}
		if (rankMax!=-1)
		{
			where += "rank<="+rankMax+" AND ";
		}
		if (director!=null)
		{
			table += ",director_movie";
			where += "director_id=" + director.getId() + " AND  director_movie.movie_id=movie.id AND ";
		}
		if (actor!=null)
		{
			table += ",actor_movie";
			where += "actor_id=" + actor.getId() + " AND  actor_movie.movie_id=movie.id AND ";
		}
		if (country!=null)
		{
			table += ",country_movie";
			where += "country_id=" + country.getId() + " AND  country_movie.movie_id=movie.id AND ";
		}
		if (geners!=null && geners.size()>0)
		{
			//table += ",genre_movie";
			String nestedQuery = "(SELECT count(*) FROM genre_movie,genre WHERE genre_id=genre.id AND movie.id=genre_movie.movie_id AND genre.id IN "+ db.genreListToString(geners) +")";
			
			where += geners.size()+" IN "+nestedQuery+" AND ";
		}
		
		
		if (where!="")
		{
			where = where.substring(0,where.length()-5);//deleting the last 5 characters that are always " AND "
			where = " WHERE " + where;
		}
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM "+ table + where +" ORDER BY movie.name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Movie movie = new Movie(rs.getInt("id"),rs.getString("name"),rs.getInt("release_year"),rs.getFloat("rank"),rs.getInt("votes"));
				movies.add(movie);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get movies - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return movies;
	}

	public static synchronized List<Director> getMovieDirectors(Movie movie, int from, int to, DB db) throws DB_Error{
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Director> directors = new ArrayList<Director>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM director,director_movie WHERE director.id=director_id AND movie_id="+movie.getId()+" ORDER BY director.last_name, director.first_name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Director director = new Director(rs.getInt("id"),rs.getString("first_name"),rs.getString("last_name"));
				directors.add(director);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get directors from movie - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return directors;
	}
	
	public static synchronized List<Actor> getMovieActors(Movie movie, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Actor> actors = new ArrayList<Actor>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM actor,actor_movie WHERE actor.id=actor_id AND movie_id="+movie.getId()+" ORDER BY actor.last_name, actor.first_name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Actor actor = new Actor(rs.getInt("id"),rs.getString("first_name"),rs.getString("last_name"),rs.getInt("Sex")==1?SEX.MALE:SEX.FEMALE);
				actors.add(actor);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get actors from movie - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return actors;
	}

	public static synchronized List<Genre> getMovieGenres(Movie movie, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Genre> genres = new ArrayList<Genre>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM genre,genre_movie WHERE genre.id=genre_id AND movie_id="+movie.getId()+" ORDER BY genre.name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Genre genre = new Genre(rs.getInt("id"),rs.getString("name"));
				genres.add(genre);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get genres from movie - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return genres;
	}

	public static synchronized List<Country> getMovieCountries(Movie movie, int from, int to, DB db) throws DB_Error {
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		List<Country> countries = new ArrayList<Country>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM country,country_movie WHERE country.id=country_id AND movie_id="+movie.getId()+" ORDER BY country.name LIMIT "+startResults+","+lengthOfResults);
			while (rs.next()){
				Country country = new Country(rs.getInt("id"),rs.getString("name"));
				countries.add(country);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get countries from movie - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		return countries;
	}
	
	public static List<Movie> getMoviesForEntity(int entityId, int from, int to, DB db, String table) throws DB_Error{
		List<Movie> movieList = new ArrayList<Movie>();
		int startResults = from;
		int lengthOfResults = to - from;
		Connection conn=db.dequeueConnection();
		Statement stmt;
		ResultSet rs;
		try {
			stmt = conn.createStatement();
			String query =
					"SELECT id, name, release_year, rank  FROM movie,"+ table + "_movie" + 
					" WHERE " + table +"_id = " + entityId +" AND movie_id=id"
					+" ORDER BY movie.name LIMIT "+startResults+","+lengthOfResults;
			rs = stmt.executeQuery(query);
			while (rs.next()){
				Movie movie = new Movie(rs.getInt("id"),rs.getString("name"),rs.getInt("release_year"), 
						rs.getFloat("rank"));
				movieList.add(movie);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			db.enqueueConnection(conn);
			throw new DB_Error("ERROR get movie for " + table + " - " + e.getMessage());
		}
		db.enqueueConnection(conn);
		
		return movieList;
	}
	
	
	
}
